import mysql.connector
from mysql.connector import Error
from datetime import datetime
import json

class Database:
    def __init__(self):
        self.connection = self.create_connection()
        self.create_tables()

    def create_connection(self):
        """创建数据库连接"""
        try:
            connection = mysql.connector.connect(
                host='localhost',
                port=3306,
                user='root',
                password='123456',
                database='weather-emergency-system'
            )
            print("MySQL数据库连接成功")
            return connection
        except Error as e:
            print(f"连接MySQL数据库时出错: '{e}'")
            return None

    def create_tables(self):
        """创建所有必要的数据库表（包含自动推送功能所需的字段）"""
        try:
            cursor = self.connection.cursor()
            
            # ====================== 用户表 ======================
            # 必须先创建用户表，因为其他表有外键依赖
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(50) NOT NULL UNIQUE,  -- 用户名（唯一）
                    password VARCHAR(100) NOT NULL,        -- 密码（存储时应加密）
                    role ENUM('admin', 'rescuer', 'user') NOT NULL,  -- 角色类型
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP   -- 创建时间
                )
            """)
            
            # =============== 应急响应信息表（关键修改点） ===============
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS emergency_responses (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    content TEXT NOT NULL,                  -- 响应内容
                    admin_id INT NOT NULL,                  -- 发布管理员ID
                    is_auto BOOLEAN DEFAULT FALSE,          -- 是否自动生成
                    is_pushed BOOLEAN DEFAULT FALSE,        -- 是否已推送（新增字段）
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 发布时间
                    FOREIGN KEY (admin_id) REFERENCES users(id)  -- 外键约束
                )
            """)
            
            # ===================== 天气预警表 =====================
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS weather_warnings (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    location_id VARCHAR(20) NOT NULL,       -- 地理位置ID
                    warning_data JSON NOT NULL,             -- 预警JSON数据
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 记录时间
                )
            """)
            
            # ===================== 求救信息表 =====================
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS help_requests (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,                   -- 发送用户ID
                    content TEXT NOT NULL,                  -- 求救内容
                    status ENUM('pending', 'processed') DEFAULT 'pending',  -- 处理状态
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 发送时间
                    FOREIGN KEY (user_id) REFERENCES users(id)  -- 外键约束
                )
            """)
            
            # ====================== 消息表 =======================
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS messages (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    sender_id INT NOT NULL,                -- 发送者ID
                    receiver_id INT NOT NULL,              -- 接收者ID
                    content TEXT NOT NULL,                 -- 消息内容
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 发送时间
                    FOREIGN KEY (sender_id) REFERENCES users(id),   -- 双外键约束
                    FOREIGN KEY (receiver_id) REFERENCES users(id)
                )
            """)
            
            # ================ 初始化默认管理员账户 ================
            cursor.execute("SELECT * FROM users WHERE username = 'admin'")
            if not cursor.fetchone():  # 如果admin账户不存在
                cursor.execute("""
                    INSERT INTO users (username, password, role) 
                    VALUES ('admin', 'admin123', 'admin')  -- 默认密码应加密
                """)
                print("已创建默认管理员账户: admin/admin123")
            
            self.connection.commit()
            print("所有数据库表创建/验证完成")
        except Error as e:
            print(f"[严重错误] 数据库初始化失败: {e}")
            # 生产环境应记录日志并终止启动
            raise RuntimeError("数据库表创建失败，请检查错误日志") from e
    
    # 用户相关操作
    def add_user(self, username, password, role):
        """添加新用户"""
        try:
            cursor = self.connection.cursor()
            cursor.execute("""
                INSERT INTO users (username, password, role) 
                VALUES (%s, %s, %s)
            """, (username, password, role))
            self.connection.commit()
            return True
        except Error as e:
            print(f"添加用户时出错: '{e}'")
            return False
    
    def get_user(self, username):
        """获取用户信息"""
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
            return cursor.fetchone()
        except Error as e:
            print(f"获取用户时出错: '{e}'")
            return None
    
    def get_all_users(self, role=None):
        """获取所有用户或特定角色的用户"""
        try:
            cursor = self.connection.cursor(dictionary=True)
            if role:
                cursor.execute("SELECT * FROM users WHERE role = %s", (role,))
            else:
                cursor.execute("SELECT * FROM users")
            return cursor.fetchall()
        except Error as e:
            print(f"获取用户列表时出错: '{e}'")
            return []
    
    # 天气预警相关操作
    def add_weather_warning(self, location_id, warning_data):
        """添加天气预警"""
        try:
            cursor = self.connection.cursor()
            cursor.execute("""
                INSERT INTO weather_warnings (location_id, warning_data) 
                VALUES (%s, %s)
            """, (location_id, warning_data))
            self.connection.commit()
            return True
        except Error as e:
            print(f"添加天气预警时出错: '{e}'")
            return False
    
    def get_latest_weather_warning(self, location_id):
        """获取最新的天气预警"""
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute("""
                SELECT * FROM weather_warnings 
                WHERE location_id = %s 
                ORDER BY timestamp DESC 
                LIMIT 1
            """, (location_id,))
            return cursor.fetchone()
        except Error as e:
            print(f"获取天气预警时出错: '{e}'")
            return None
    
    # 应急响应相关操作
    def add_emergency_response(self, content, admin_id):
        """添加应急响应信息"""
        try:
            cursor = self.connection.cursor()
            cursor.execute("""
                INSERT INTO emergency_responses (content, admin_id) 
                VALUES (%s, %s)
            """, (content, admin_id))
            self.connection.commit()
            return cursor.lastrowid
        except Error as e:
            print(f"添加应急响应时出错: '{e}'")
            return None
    
    def get_latest_emergency_response(self):
        """获取最新的应急响应信息"""
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute("""
                SELECT er.*, u.username as admin_name 
                FROM emergency_responses er
                JOIN users u ON er.admin_id = u.id
                ORDER BY timestamp DESC 
                LIMIT 1
            """)
            return cursor.fetchone()
        except Error as e:
            print(f"获取应急响应时出错: '{e}'")
            return None
    
    # 求救信息相关操作
    def add_help_request(self, user_id, content):
        """添加求救信息"""
        try:
            cursor = self.connection.cursor()
            cursor.execute("""
                INSERT INTO help_requests (user_id, content) 
                VALUES (%s, %s)
            """, (user_id, content))
            self.connection.commit()
            return cursor.lastrowid
        except Error as e:
            print(f"添加求救信息时出错: '{e}'")
            return None
    
    def get_all_help_requests(self):
        """获取所有求救信息"""
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute("""
                SELECT hr.*, u.username as user_name 
                FROM help_requests hr
                JOIN users u ON hr.user_id = u.id
                ORDER BY timestamp DESC
            """)
            return cursor.fetchall()
        except Error as e:
            print(f"获取求救信息时出错: '{e}'")
            return []
    
    def update_help_request_status(self, request_id, status):
        """更新求救信息状态"""
        try:
            cursor = self.connection.cursor()
            cursor.execute("""
                UPDATE help_requests 
                SET status = %s 
                WHERE id = %s
            """, (status, request_id))
            self.connection.commit()
            return True
        except Error as e:
            print(f"更新求救信息状态时出错: '{e}'")
            return False
    
    # 消息相关操作
    def add_message(self, sender_id, receiver_id, content):
        """添加消息"""
        try:
            cursor = self.connection.cursor()
            cursor.execute("""
                INSERT INTO messages (sender_id, receiver_id, content) 
                VALUES (%s, %s, %s)
            """, (sender_id, receiver_id, content))
            self.connection.commit()
            return cursor.lastrowid
        except Error as e:
            print(f"添加消息时出错: '{e}'")
            return None
    
    def get_messages_for_user(self, user_id, sender_id=None):
        """获取用户的消息"""
        try:
            cursor = self.connection.cursor(dictionary=True)
            if sender_id:
                cursor.execute("""
                    SELECT m.*, u.username as sender_name 
                    FROM messages m
                    JOIN users u ON m.sender_id = u.id
                    WHERE m.receiver_id = %s AND m.sender_id = %s
                    ORDER BY m.timestamp DESC
                """, (user_id, sender_id))
            else:
                cursor.execute("""
                    SELECT m.*, u.username as sender_name 
                    FROM messages m
                    JOIN users u ON m.sender_id = u.id
                    WHERE m.receiver_id = %s
                    ORDER BY m.timestamp DESC
                """, (user_id,))
            return cursor.fetchall()
        except Error as e:
            print(f"获取消息时出错: '{e}'")
            return []
    
    def get_conversation(self, user1_id, user2_id):
        """获取两个用户之间的对话"""
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute("""
                SELECT m.*, 
                       u1.username as sender_name,
                       u2.username as receiver_name
                FROM messages m
                JOIN users u1 ON m.sender_id = u1.id
                JOIN users u2 ON m.receiver_id = u2.id
                WHERE (m.sender_id = %s AND m.receiver_id = %s)
                   OR (m.sender_id = %s AND m.receiver_id = %s)
                ORDER BY m.timestamp
            """, (user1_id, user2_id, user2_id, user1_id))
            return cursor.fetchall()
        except Error as e:
            print(f"获取对话时出错: '{e}'")
            return []
        
def add_weather_warning(self, location_id, warning_data):
    """添加天气预警"""
    try:
        cursor = self.connection.cursor()
        cursor.execute("""
            INSERT INTO weather_warnings (location_id, warning_data) 
            VALUES (%s, %s)
        """, (location_id, json.dumps(warning_data)))
        self.connection.commit()
        return True
    except Error as e:
        print(f"添加天气预警时出错: '{e}'")
        return False

#检查是否有新预警需要通知   
def get_unnotified_warnings(self):
    """获取尚未通知用户的新预警"""
    try:
        cursor = self.connection.cursor(dictionary=True)
        cursor.execute("""
            SELECT * FROM weather_warnings 
            WHERE notified = 0
            ORDER BY timestamp DESC
            LIMIT 1
        """)
        return cursor.fetchone()
    except Error as e:
        print(f"获取未通知预警时出错: '{e}'")
        return None

def mark_warning_as_notified(self, warning_id):
    """标记预警为已通知"""
    try:
        cursor = self.connection.cursor()
        cursor.execute("""
            UPDATE weather_warnings 
            SET notified = 1 
            WHERE id = %s
        """, (warning_id,))
        self.connection.commit()
        return True
    except Error as e:
        print(f"标记预警为已通知时出错: '{e}'")
        return False

def mark_as_pushed(self, response_id):
    """标记响应为已推送"""
    try:
        cursor = self.connection.cursor()
        cursor.execute("""
            UPDATE emergency_responses 
            SET is_pushed = TRUE 
            WHERE id = %s
        """, (response_id,))
        self.connection.commit()
        return True
    except Error as e:
        print(f"标记推送状态失败: {e}")
        return False

def get_latest_emergency_response(self):
    """获取最新的应急响应信息（包含 is_pushed 字段）"""
    try:
        cursor = self.connection.cursor(dictionary=True)
        cursor.execute("""
            SELECT 
                er.id, 
                er.content, 
                er.admin_id, 
                er.is_auto, 
                er.is_pushed, 
                er.timestamp,
                u.username as admin_name
            FROM emergency_responses er
            JOIN users u ON er.admin_id = u.id
            ORDER BY er.timestamp DESC 
            LIMIT 1
        """)
        return cursor.fetchone()
    except Error as e:
        print(f"获取应急响应时出错: '{e}'")
        return None
    
# 创建全局数据库实例
db = Database()